--Согласно разработаннной вами модели БД (практическая работа 3):
--
--Создайте БД
--Реализуйте таблицы для хранения требуемых данных.
--Определите в таблицах все необходимые ограничения целостности:
--Первичные ключи и альтернативные ключи
--Внешние ключи
--Ограничения CHECK и DEFAULT
--Средствами оболочки SQL Server Management Studio 
--или DBeaver создайте схему вашей БД, отражающую все таблицы и связи между ними.
--Реализуйте 2 представления, 
--которые позволят получать из вашей БД основную информацию, 
--соответсвующую предметной области. 

--По желанию:
--Реализуйте 1 хранимую процедуру с входными параметрами
--Реализуйте 1 табличную функцию с входными параметрами

CREATE DATABASE [policlinic];

GO
USE [policlinic];

CREATE SCHEMA [catalogs];

GO
CREATE TABLE [catalogs].[cities]
(cityID int Not NULL IDENTITY (100,1) 
 	CONSTRAINT PK_cityID 
 	PRIMARY KEY,
 city_name nvarchar(50) Not NULL 
 	CONSTRAINT UQ_city_name 
 	UNIQUE);

INSERT INTO [catalogs].[cities]
VALUES (N'Всеволожск'), (N'Выборг'), (N'Гатчина'), 
	   (N'Красное Село'), (N'Кронштадт'), (N'Петергоф'),
	   (N'Пушкин'), (N'Санкт-Петербург'), (N'Сестрорецк');;
GO
CREATE TABLE [catalogs].[districts]
(districtID int Not NULL IDENTITY(10,1) 
 	CONSTRAINT PK_districtID 
 	PRIMARY KEY,
 district_name nvarchar(50) Not NULL 
	CONSTRAINT UQ_district_name 
	UNIQUE);

INSERT INTO [catalogs].[districts]
VALUES (N'Без района'), (N'Приморский'), (N'Выборгский'), (N'Калининский'), 
	   (N'Петроградский'), (N'Красногвардейский'), (N'Василеостровский'),
	   (N'Центральный'), (N'Адмиралтейский'), (N'Кировский'),
	   (N'Невский'), (N'Фрунзенский'), (N'Московский');

GO	  
CREATE TABLE [catalogs].[streets]
(streetID int Not NULL IDENTITY(0,10)
	CONSTRAINT PK_streetID 
	PRIMARY KEY,
 street_name nvarchar(50) Not NULL 
 	CONSTRAINT UQ_street_name 
 	UNIQUE);

INSERT INTO [catalogs].[streets]
VALUES (N'улица Карла Маркса'), (N'Приморский проспект'), 
		(N'Выборгская набережная'), (N'Гражданский проспект'),
	    (N'Большой проспект Петроградской стороны'), (N'Шоссе Революции'),
		(N'Средний проспект Васильевского острова'), 
		(N'Невский проспект'), (N'Набережная канала Грибоедова'),
		(N'Улица Зои Космодемьянской'), 
	    (N'Набережная Обводного канала'), (N'Улица Ярослава Гашека'),
	    (N'аллея Поликарпова'), (N'проспект Королёва'),(N'Серебристый бульвар'),
		(N'аллея Котельникова'),(N'Коломяжский проспект'),
		(N'Ипподромный переулок');
	
GO	   
CREATE TABLE [catalogs].[diseases]
(ICD char (6) Not NULL
	CONSTRAINT PK_ICD 
	PRIMARY KEY,
 disease_name nvarchar(150) Not NULL
 	CONSTRAINT UQ_disease_name 
 	UNIQUE);
 
INSERT INTO [catalogs].[diseases]
VALUES ('K25', N'Язва желудка'),('K29', N'Гастрит и дуоденит'),
		('Z00', N'Общий осмотр и обследование лиц, не имеющих жалоб или установ ленного диагноза'),
		('E01', N'Болезни щитовидной железы, связанные с йодной недостаточностью, и сходные состояния'),
		('E11', N'Инсулиннезависимый сахарный диабет'),
		('F32.0', N'Депрессивный эпизод легкой степени');
	
GO
CREATE TABLE [catalogs].[specializations]
(specializationID int Not NULL IDENTITY
	CONSTRAINT PK_specializationID 
	PRIMARY KEY,
 specialization_name nvarchar(50) Not NULL 
 	CONSTRAINT UQ_specialization_name 
 	UNIQUE);
 
INSERT INTO [catalogs].[specializations]
VALUES (N'Гастроэнтеролог'),(N'Окулист'),(N'Эндокринолог'),(N'Психиатр');

GO
CREATE TABLE [catalogs].[areas]
(area# tinyint Not NULL PRIMARY KEY
	CONSTRAINT [CHECK_Area#(>0)] 
 	CHECK (area# > 0));

INSERT INTO [catalogs].[areas]
VALUES (1),(2),(3);

GO
CREATE TABLE [catalogs].[area_adresses]
(adressID int Not NULL IDENTITY
	CONSTRAINT adressID 
	PRIMARY KEY,
 cityID int Not NULL 
 	CONSTRAINT FK_catalogs_cities_cityID 
 	FOREIGN KEY (cityID) REFERENCES [catalogs].[cities](cityID),
 districtID int Not NULL
 	CONSTRAINT FK_catalogs_districts_districtID 
 	FOREIGN KEY (districtID) REFERENCES [catalogs].[districts](districtID),
 streetID int Not NULL
 	CONSTRAINT FK_catalogs_streets_streetID 
 	FOREIGN KEY (streetID) REFERENCES [catalogs].[streets](streetID),
 building# smallint Not NULL
 	CONSTRAINT [CHECK_building#(>0)] 
 	CHECK (building# > 0),
 area# tinyint Not NULL
 	CONSTRAINT FK_catalogs_areas_area# 
 	FOREIGN KEY (area#) REFERENCES [catalogs].[areas](area#),
CONSTRAINT UQ_adress 
UNIQUE (cityID,districtID,streetID,building#), 	
CONSTRAINT UQ_adress_and_area 
UNIQUE (cityID,districtID,streetID,building#,area#));

INSERT INTO [catalogs].[area_adresses]
VALUES (107,11,170,5,1),(107,11,170,3,1),(107,11,170,7,1),(107,11,170,9,1),
		(107,11,180,3,1),(107,11,180,1,1),
		(107,11,170,28,2),(107,11,130,3,2),(107,11,140,2,2),
		(107,11,130,1,3),(107,11,130,5,3),(107,11,140,7,3),
		(107,11,150,26,3),(107,11,150,28,3);

GO
CREATE SCHEMA [HR];

GO 
CREATE TABLE [HR].[Doctors]
(doctorID int Not NULL IDENTITY
	CONSTRAINT PK_doctorID 
	PRIMARY KEY,
 surname nvarchar(50) Not NULL,
 name nvarchar(50) Not NULL,
 second_name nvarchar(50) NULL,
 birth_date date Not NULL 
 	CONSTRAINT [CHECK_birth_date(age>18)] 
 	CHECK (DATEDIFF(year,birth_date,getdate()) > 18 
 			AND DATEDIFF(year,birth_date,getdate()) < 70),
 cityID int Not NULL 
 	CONSTRAINT FK_catalogs_cities_cityID 
 	FOREIGN KEY (cityID) REFERENCES [catalogs].[cities](cityID),
 districtID int Not NULL
 	CONSTRAINT FK_catalogs_districts_districtID 
 	FOREIGN KEY (districtID) REFERENCES [catalogs].[districts](districtID),
 streetID int Not NULL
 	CONSTRAINT FK_catalogs_streets_streetID 
 	FOREIGN KEY (streetID) REFERENCES [catalogs].[streets](streetID),
 building# smallint Not NULL
 	CONSTRAINT [CHECK_building#(>0)] 
 	CHECK (building# > 0),
 specialization bit Not NULL,
CONSTRAINT CONSTRAINT UQ_surname_name_birth_date_cityID_streetID_building# 
	UNIQUE (surname,name,birth_date, cityID, streetID, building#),
CONSTRAINT UQ_doctorID_specialization
	UNIQUE (doctorID,specialization));

INSERT INTO [HR].[Doctors]
VALUES (N'Павлов', N'Иван', N'Петрович', '19871005', 107, 14, 40, 7, 1),
		(N'Пепи', N'Анк', N'Ири', '19991227', 107, 20, 100, 4, 1),
		(N'Дедов', N'Иван', N'Иванович', '19650101', 107, 11, 10, 78, 1),
		(N'Freud', N'Sigismund', N'Schlomo', '19560506', 107, 15, 50, 156, 1),
		(N'Иванов', N'Иван', N'Иванович', '20001103', 107, 22, 120, 42, 0),
		(N'Петров', N'Петр', N'Петрович', '19950317', 104, 10, 0, 109, 0),
		(N'Сидоров', N'Сидор', N'Сидорович', '19710326', 107, 14, 40, 105, 0);

GO
CREATE TABLE [HR].[Area_doctors]
(doctorID int Not NULL
	CONSTRAINT UQ_doctorID
	UNIQUE,
 specialization bit Not NULL
 	CONSTRAINT [CHECK_specialization<1]
 	CHECK (specialization<1),
 area# tinyint Not NULL
 	CONSTRAINT UQ_area#
	UNIQUE
	CONSTRAINT FK_catalogs_areas_area# 
 	FOREIGN KEY (area#) REFERENCES [catalogs].[areas](area#)
 	ON UPDATE CASCADE,
 CONSTRAINT FK_HR_Doctors_doctorID_specialization 
 	FOREIGN KEY (doctorID,specialization) 
 	REFERENCES [HR].[Doctors](doctorID,specialization)
 	ON DELETE CASCADE ON UPDATE CASCADE);

INSERT INTO [HR].[Area_doctors]
VALUES (5,0,1),(6,0,2),(7,0,3);

GO
CREATE TABLE [HR].[Specialists]
(doctorID int Not NULL
	CONSTRAINT UQ_doctorID_Specialists
	UNIQUE,
specialization bit Not NULL
 	CONSTRAINT [CHECK_specialization>0]
 	CHECK (specialization>0),
specializationID int Not NULL
 	CONSTRAINT FK_catalogs_specializations_specializationID 
 	FOREIGN KEY (specializationID) 
 	REFERENCES [catalogs].[specializations](specializationID)
 	ON UPDATE CASCADE,
 CONSTRAINT FK_HR_Doctors_doctorID__specialization_Specialists
 	FOREIGN KEY (doctorID,specialization) 
 	REFERENCES [HR].[Doctors](doctorID,specialization)
 	ON DELETE CASCADE ON UPDATE CASCADE);

INSERT INTO [HR].[Specialists]
VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4);

GO
CREATE TABLE [HR].[Schedules]
(scheduleID int Not NULL IDENTITY(1000,1)
	CONSTRAINT PK_scheduleID 
	PRIMARY KEY,
 doctorID int Not NULL
 	CONSTRAINT FK_HR_Scheduless_doctorID
 	FOREIGN KEY (doctorID) 
 	REFERENCES [HR].[Doctors](doctorID)
 	ON DELETE CASCADE ON UPDATE CASCADE,
 smena# bit Not NULL DEFAULT(0),
 Week_day tinyint Not NULL
 	CONSTRAINT [CHECK_Week_day(>0 && <=5)]
 	CHECK(Week_day>0 AND Week_day<=5),
 kabinet# smallint Not NULL,
 CONSTRAINT UQ_doctorID_smena#_Week_day_kabinet#
 	UNIQUE(doctorID,smena#, Week_day, kabinet#),
 CONSTRAINT UQ_smena#_Week_day_kabinet#
	UNIQUE(smena#, Week_day, kabinet#));

INSERT INTO [HR].[Schedules]
VALUES (1,DEFAULT,5,101),
		(2,DEFAULT,5,102),
		(3,DEFAULT,5,103),
		(4,DEFAULT,5,104),
		(5,1,5,101),
		(6,1,5,102),
		(7,1,5,103),
		(4,1,5,104);

GO 
CREATE TABLE [dbo].[Patients]
(patientID int Not NULL IDENTITY
	CONSTRAINT PK_patientID 
	PRIMARY KEY,
 surname nvarchar(50) Not NULL,
 name nvarchar(50) Not NULL,
 second_name nvarchar(50) Not NULL 
 	DEFAULT(N'нет отчества'),
 birth_date date Not NULL 
 	CONSTRAINT [CHECK_birth_date(age>18)] 
 	CHECK (DATEDIFF(year,birth_date,getdate()) > 18 
 			AND DATEDIFF(year,birth_date,getdate()) < 150),
 adressID int Not NULL
	CONSTRAINT FK_Patients_adressID 
 	FOREIGN KEY (adressID) REFERENCES [catalogs].[area_adresses](adressID),
 appartament# smallint NULL
 	CONSTRAINT CHECK_appartament#
 	CHECK(appartament#>0),
 m_phone# char(16) NULL
 	CONSTRAINT CHECK_m_phone#
 	CHECK(m_phone# LIKE '+7([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]')
 	CONSTRAINT UQ_m_phone# 
	UNIQUE (m_phone#),
 work_place nvarchar(100) Not NULL DEFAULT(N'Г­ГҐ Г°Г ГЎГ®ГІГ ГҐГІ'),
 CONSTRAINT UQ_surname_name_birth_date_adressID 
	UNIQUE (surname,name,birth_date, adressID));

INSERT INTO [dbo].[Patients]
VALUES (N'Упифанова',N'Марфа',DEFAULT,'19251002', 46, 14,'+7(931)208-14-85',DEFAULT),
		(N'Андреянов', N'Андрей', N'Артемович','19751012' , 46, 29,'+7(931)208-23-78', N'ООО "Ромашка", грузчик'),
		(N'Трифонов',N'Елисей',N'Аркадиевич','19511231', 46, 107,NULL, DEFAULT),
		(N'Трифонов',N'Елисей',N'Аркадиевич','19511231', 47, 14,'+7(931)208-11-00' ,N'OOO"Цветик", иллюстратор'),
		(N'Антонова',N'Алевтина',N'Илларионовна','19981014',48 ,NULL,'+7(911)105-11-00', DEFAULT),
		(N'Петров',N'Петр',N'Петрович','19710421', 49,107 ,'+7(911)105-14-01', DEFAULT),
		(N'Петров',N'Петр',N'Петрович','19900117', 49, 107,'+7(911)105-14-02', N'НАО"НСК", оператор'),
		(N'Иванова',N'Екатерина',N'Михайловна','19880124' ,55 , 4,'+7(911)102-14-02' ,N'Интехпромком, астронавт'),
		(N'Гальванов',N'Игорь',N'Алексеевич','19541003' , 54,NULL,'+7(931)102-14-02', DEFAULT),
		(N'Ульянова',N'Ольга',N'Иоановна','19990203' ,58 ,3,'+7(911)111-11-12' ,N'ООО "РПЦ", послушница'),
		(N'Ольхова',N'Епифания',N'Сергеевна','20001221' ,59 ,245,'+7(931)192-19-92', DEFAULT),
		(N'Сергеев',N'Сергей',N'Сергеевич','19950206' ,50 ,45,'+7(911)112-10-92', DEFAULT),
		(N'Иванов',N'Иван',N'Иванович','20010923' ,51 ,24,'+7(921)102-11-92', DEFAULT);

GO 		
CREATE TABLE [dbo].[Check_numbers]
(patient# tinyint Not NULL
	CONSTRAINT [CHECK_patient#(>0)]
	CHECK(patient#>0),
 scheduleID int Not NULL
 	CONSTRAINT FK_Check_numbers_scheduleID
 	FOREIGN KEY (scheduleID) REFERENCES [HR].[Schedules](scheduleID),
 patientID int Not NULL
	CONSTRAINT FK_Check_numbers_patientID 
 	FOREIGN KEY (patientID) REFERENCES [dbo].[Patients](patientID),
CONSTRAINT PK_patient#_scheduleID_patientID
PRIMARY KEY(patient#,scheduleID,patientID),
CONSTRAINT UQ_patient#_scheduleID
UNIQUE (patient#,scheduleID));

INSERT INTO [dbo].[Check_numbers]
VALUES (1,1000,2), (2,1000,3),(3,1000,4),
		(1,1002,1),(2,1002,8),(3,1002,9),(4,1002,13),
		(1,1003,5),(2,1003,6),(3,1003,7),
		(1,1004,10),(1,1005,11),
		(1,1006,12),(2,1006,13),(1,1007,1);	
	
GO 
CREATE TABLE [dbo].[Diagnoses]
(patient# tinyint Not NULL,
 scheduleID int Not NULL,
 patientID int Not NULL,
 ICD char (6) Not NULL
 	CONSTRAINT FK_Diagnosess_ICD 
 	FOREIGN KEY (ICD) REFERENCES [catalogs].[diseases](ICD),
 chronicID bit Not NULL DEFAULT(0),
 Data_priema date Not NULL DEFAULT(GETDATE())
 	CONSTRAINT CHECK_Data_priema
 	CHECK(DATEDIFF(day,Data_priema,getdate()) <= 2 
 			AND DATEDIFF(day,Data_priema,getdate()) >=0),
CONSTRAINT PK_patientID_ICD_Data_priema
	PRIMARY KEY (patientID,ICD,Data_priema),
CONSTRAINT FK_Diagnosess_patient#_scheduleID_patientID
	FOREIGN KEY (patient#,scheduleID,patientID) 
	REFERENCES [dbo].[Check_numbers](patient#,scheduleID,patientID));
 
INSERT INTO [dbo].[Diagnoses]
VALUES 	(1,1002,1,'E01', 1, DEFAULT),
		(1,1000,2,'K29', 0, DEFAULT),
		(2,1000,3,'K29', 0, DEFAULT),
		(3,1000,4,'K29', 1, DEFAULT),
		(1,1003,5,'F32.0', 1, '20220421'),
		(2,1003,6,'F32.0', 0, '20220420'),
		(3,1003,7,'F32.0', 0, DEFAULT),
		(2,1002,8,'E11', 1, DEFAULT),
		(3,1002,9,'E11', 1, DEFAULT),
		(1,1004,10,'Z00', DEFAULT, DEFAULT),
		(1,1005,11,'Z00', DEFAULT, DEFAULT),
		(1,1006,12,'Z00', DEFAULT, DEFAULT),
		(2,1006,13,'Z00', DEFAULT, DEFAULT),
		(4,1002,13,'E01', 1,DEFAULT),
		(1,1007,1,'F32.0', 1, DEFAULT);

GO
CREATE VIEW Diagnostic_cards
AS 
SELECT dbo_Pat.surname, dbo_Pat.name, dbo_Pat.second_name, dbo_Pat.birth_date,
		dbo_Dg.Data_priema,diseases.disease_name,
		IIF(dbo_Dg.chronicID>0,N'хроническое', N'острое') AS "chronic"
FROM DevDB2022_svecyk.dbo.Patients AS dbo_Pat
	JOIN DevDB2022_svecyk.dbo.Diagnoses AS dbo_Dg
	ON dbo_Pat.patientID = dbo_Dg.patientID
	JOIN DevDB2022_svecyk.catalogs.diseases AS diseases
	ON diseases.ICD  = dbo_Dg.ICD;

SELECT *
FROM Diagnostic_cards;

GO
CREATE Function fn_Diagnostic_cards (@patientid int)
RETURNS table
AS 
RETURN
(SELECT dbo_Pat.surname, dbo_Pat.name, dbo_Pat.second_name, dbo_Pat.birth_date,
		dbo_Dg.Data_priema,diseases.disease_name,
		IIF(dbo_Dg.chronicID>0,N'хроническое', N'острое') AS "chronic"
FROM DevDB2022_svecyk.dbo.Patients AS dbo_Pat
	JOIN DevDB2022_svecyk.dbo.Diagnoses AS dbo_Dg
	ON dbo_Pat.patientID = dbo_Dg.patientID and dbo_Pat.patientID = @patientid
	JOIN DevDB2022_svecyk.catalogs.diseases AS diseases
	ON diseases.ICD  = dbo_Dg.ICD);

SELECT *
FROM fn_Diagnostic_cards(13);
GO
CREATE PROCEDURE Find_cityID
@cityname AS nvarchar(50), @cityID AS int OUTPUT
AS
SET @cityID = (SELECT cities.cityID
				FROM DevDB2022_svecyk.catalogs.cities AS cities
				WHERE cities.city_name = @cityname);

CREATE PROCEDURE Find_districtID
@districtname AS nvarchar(50), @districtID AS int OUTPUT
AS
SET @districtID = (SELECT districts.districtID 
					FROM DevDB2022_svecyk.catalogs.districts AS districts
					WHERE districts.district_name = @districtname);
GO
CREATE PROCEDURE Find_streetID
@streetname AS nvarchar(50), @streetID AS int OUTPUT
AS
SET @streetID = (SELECT street.streetID 
					FROM DevDB2022_svecyk.catalogs.streets AS street
					WHERE street.street_name = @streetname);				
GO
CREATE PROCEDURE Get_specializationID
@specialization_name AS nvarchar(50), @specializationID AS int OUTPUT
AS
SET	@specializationID = (SELECT specializationID
						FROM DevDB2022_svecyk.catalogs.specializations
						WHERE specialization_name = @specialization_name);
GO
CREATE PROCEDURE Add_doctor
@surname AS nvarchar(50), @name AS nvarchar(50), @second_name AS nvarchar(50) = N'нет отчества',
@birth_date AS date, @cityname AS nvarchar(50) = N'Санкт-Петербург',
@districtname AS nvarchar(50), @streetname AS nvarchar(50), @building# AS smallint,
@specialization AS bit, 
@specialization_name AS nvarchar(50) = N'n/a',
@area# AS tinyint = 0
AS

DECLARE @rescityID AS int;
DECLARE @resdistrictID AS int;
DECLARE @resstreet AS int;

EXEC Find_cityID @cityname, @rescityID OUTPUT;
EXEC Find_districtID @districtname,  @resdistrictID OUTPUT;
EXEC Find_streetID @streetname, @resstreet OUTPUT;

INSERT INTO [HR].[Doctors]
VALUES (@surname, @name, IIF(@second_name = N'нет отчества', NULL, @second_name),
@birth_date, @rescityID,@resdistrictID, @resstreet,@building#, @specialization);

IF @specialization<1
INSERT INTO [HR].[Area_doctors]
VALUES ((SELECT max(doctorID) FROM [HR].[Doctors]),0,@area#)
ELSE 
DECLARE @resspecializationID AS int;
EXEC Get_specializationID @specialization_name, @resspecializationID OUTPUT;
INSERT INTO [HR].[Specialists]
VALUES ((SELECT max(doctorID) FROM [HR].[Doctors]),1,@resspecializationID);

EXEC Add_doctor N'Пименов', N'Иван', N'Петрович', '19871005',
				N'Красное Село', N'Без района', N'улица Карла Маркса', 7, 1, N'Психиатр'
SELECT TOP 1 *
FROM [HR].[Doctors]
ORDER BY doctorID DESC 
GO